ALTER PROCEDURE sp_create_update_inventory_receive_lot
(
	@SHIP_LOC_PROCESSSTEP_ID	INT,
	@LOT_NUMBER		 VARCHAR(50),
	@FAB_LOT_NUMBER  VARCHAR(50),
	@QTY_SHIPPED     INT,
	@VENDOR_NAME     VARCHAR(50),
	@INVENTORY_TYPE  VARCHAR(50),
	@USER_NAME		 VARCHAR(50),
	@PREV_SHIPLOT_ID INT,
	@PREV_LOT_ID	 INT,
    @DATE_CODE		 VARCHAR(50)
)
AS 
BEGIN
	DECLARE @LOCATION_ID		INT
	DECLARE @PROCESS_STEP_ID	INT
	DECLARE @DATE_IN			DATETIME
	DECLARE @TRANSIT_LOT_COUNT	INT
    DECLARE @AssemblyReleaseId  INT
	
	SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
	
	SELECT TOP 1 @AssemblyReleaseId = ART.ASSEMBLY_RELEASE_ID FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_Lot_TABLE ARLT 
					WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND SHIPPED_LOT_NUMBER = @LOT_NUMBER AND STATUS NOT IN ('CANCELLED', 'Closed')
		
	IF @INVENTORY_TYPE = 'DIE BANK'
	BEGIN
			SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME

			--SELECT TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SHIP_LOC_PROCESSSTEP_ID,0 )  WHERE PS_TYPE = @INVENTORY_TYPE
			SET @PROCESS_STEP_ID = @SHIP_LOC_PROCESSSTEP_ID
	END
	ELSE
	BEGIN
			IF @INVENTORY_TYPE = 'ASSEMBLY'
			BEGIN
				SELECT  TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS NOT IN ('CANCELLED', 'Closed') AND SHipped_LOt_NUMBER like @LOT_NUMBER+'%' 		
			END
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				SELECT TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SHIP_LOC_PROCESSSTEP_ID,1 )  WHERE PS_TYPE = @INVENTORY_TYPE
				IF @PROCESS_STEP_ID IS NULL
				BEGIN
					SELECT TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SHIP_LOC_PROCESSSTEP_ID,0 )  WHERE PS_TYPE = @INVENTORY_TYPE
				END
			END
	END
	
	IF @PROCESS_STEP_ID IS NOT NULL 
	BEGIN
		IF @INVENTORY_TYPE = 'ASSEMBLY'
		BEGIN	
				SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
				EXEC sp_create_update_data_entry_assembly_lot @PROCESS_STEP_ID, @LOCATION_ID,@FAB_LOT_NUMBER, @QTY_SHIPPED, NULL,NULL,NULL,@DATE_IN,NULL,@VENDOR_NAME, NULL, 'PROD',@LOT_NUMBER, @DATE_CODE, 'Running', NULL,@USER_NAME,@PREV_SHIPLOT_ID,@PREV_LOT_ID 
		END
		ELSE IF @INVENTORY_TYPE = 'TEST' OR @INVENTORY_TYPE = 'FT' OR @INVENTORY_TYPE = 'FPO'
		BEGIN
				SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'TEST' AND L_VENDOR_NAME = @VENDOR_NAME

				EXEC sp_create_update_data_entry_test_lot @PROCESS_STEP_ID, @LOCATION_ID,@FAB_LOT_NUMBER, @QTY_SHIPPED, NULL,NULL,NULL,@DATE_IN ,NULL,@VENDOR_NAME, NULL, 'PROD',@LOT_NUMBER, @DATE_CODE, 'Running', NULL,@USER_NAME, @PREV_SHIPLOT_ID, @PREV_LOT_ID 
		END
		ELSE IF @INVENTORY_TYPE = 'DIE BANK'
		BEGIN
				EXEC sp_create_update_data_entry_inventory_lot @PROCESS_STEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, NULL, NULL,@QTY_SHIPPED, NULL,@DATE_IN,NULL,@VENDOR_NAME, NULL, 'PROD',@DATE_CODE, 'Running','', NULL , NULL, @USER_NAME,@PREV_SHIPLOT_ID,@PREV_LOT_ID 
		END
		ELSE IF @INVENTORY_TYPE = 'TAPE & REEL' OR @INVENTORY_TYPE = 'CLASS STORE' 
		BEGIN	
				SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'CLASS STORE' AND L_VENDOR_NAME = @VENDOR_NAME

				EXEC sp_create_update_data_entry_inventory_lot @PROCESS_STEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, NULL, NULL,@QTY_SHIPPED, NULL,@DATE_IN,NULL,@VENDOR_NAME, NULL, 'PROD',@DATE_CODE, 'Running','CLASS STORE', NULL , NULL, @USER_NAME,@PREV_SHIPLOT_ID,@PREV_LOT_ID
		END
		ELSE IF @INVENTORY_TYPE = 'Bake & Dry'OR @INVENTORY_TYPE = 'Mark & Pack' 
		BEGIN	
				SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Mark &amp; Pack' AND L_VENDOR_NAME = @VENDOR_NAME

				EXEC sp_create_update_data_entry_inventory_lot @PROCESS_STEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, NULL, NULL,@QTY_SHIPPED, NULL,@DATE_IN,NULL,@VENDOR_NAME, NULL, 'PROD',@DATE_CODE, 'Running','Mark & Pack', NULL , NULL, @USER_NAME,@PREV_SHIPLOT_ID,@PREV_LOT_ID
		END
		
		ELSE IF @INVENTORY_TYPE = 'FG' OR @INVENTORY_TYPE = 'FINISHED GOODS'
		BEGIN
				SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = @VENDOR_NAME

				EXEC sp_create_update_data_entry_inventory_lot @PROCESS_STEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, NULL, NULL,@QTY_SHIPPED, NULL,@DATE_IN,NULL,@VENDOR_NAME, NULL, 'PROD',@DATE_CODE, 'Running','FINISHED GOODS', NULL , NULL, @USER_NAME,@PREV_SHIPLOT_ID,@PREV_LOT_ID
		END


		SELECT @TRANSIT_LOT_COUNT = COUNT(*) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Transit'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS = 'Assembly'
				AND LT.LOT_NUMBER IN (SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS Not In ('Cancelled' , 'Closed')
							AND ART.ASSEMBLY_RELEASE_ID = @AssemblyReleaseId )

		IF @INVENTORY_TYPE <> 'Die Bank' AND @TRANSIT_LOT_COUNT <= 0
		BEGIN
				UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_ID = @AssemblyReleaseId
		END
		
	END
	
END